Hi,
I've got a system where I want to store a full history in multiple rows
as well as a current view which has the current values in one record. I
know that all of the data that I want is in the event history, it's just
somewhat involved to get it from the event history.
I have an inheritance model where:
abstract_core -+- history_foo (4 more colums than core)
\-- current_foo (same columns as core)
Where abstract_core is a large and unknown set of columns.
(currently
low hundreds in most cases)
What I currently do from an outside app:
insert into history_foo (column1, ...) values (bar, ...);
update current_foo set column1=bar, ... where pkey=a;
What I'd like to do is insert, then have a trigger/rule assemble an
update statement for the current_foo table, only updating the columns
that
are non-null and in the current_foo table.
I can live with adding the 4 extra columns from history_foo to
current_foo, they won't make logical sense, but I can hide them from
those who would be confused with well placed views.
Any clues for me on how to get pg to do this?
eric